﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;


namespace IphoneTrackerDotNet
{
    public class IphoneCellLocationDal
    {
        
        public Boolean DoesTableExist(SQLiteConnection theDatabase, String tableName)
        {
            var cmd = new SQLiteCommand(theDatabase)
                          {
                              CommandText = "SELECT name FROM sqlite_master WHERE name='" + tableName + "'"
                          };
            var rdr = cmd.ExecuteReader();
            return rdr.HasRows;
        }

        public List<Localizacao> GetLocalizcao(string dbPath)
        {
           
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

            Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US");

            var locais = new List<Localizacao>();
           
            string tbLocation = string.Empty;
            

            // create a new database connection:
            var sqliteConn = new SQLiteConnection("Data Source=" + dbPath + ";Version=3;New=True;Compress=True;");
            // open the connection:
            sqliteConn.Open();
            if (DoesTableExist(sqliteConn, "CellLocation"))
            {
                tbLocation = "CellLocation";
            }
            else
            {
                if (DoesTableExist(sqliteConn, "CdmaCellLocation"))
                {
                    tbLocation = "CdmaCellLocation";
                }
            }
            

            // create a new SQL command:
            SQLiteCommand sqliteCmd = sqliteConn.CreateCommand();

            // Let the SQLiteCommand object know our SQL-Query:
            sqliteCmd.CommandText = "SELECT  latitude,longitude,timestamp FROM WifiLocation union SELECT  latitude,longitude,timestamp FROM "+ tbLocation +";";

            var unixEpoch = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc);

            const float precision = 100;
            SQLiteDataReader sqliteDatareader = sqliteCmd.ExecuteReader();

            while (sqliteDatareader.Read()) // Read() returns true if there is still a result line to read
            {

                var l = new Localizacao
                            {
                                Latitude = sqliteDatareader.GetFloat(0),
                                Longitude = sqliteDatareader.GetFloat(1),
                                TimeStamp2 = sqliteDatareader.GetFloat(2),
                                TimeStamp = 0
                            };
                if ((l.Latitude == 0.0) && (l.Longitude == 0.0))
                    continue;

                
                const double iOsToUnixOffset = (31 * 365.25 * 24 * 60 * 60);
                //float weekInSeconds = (7 * 24 * 60 * 60);
                DateTime data = unixEpoch.AddSeconds(l.TimeStamp2 + iOsToUnixOffset);


                var latitudeIndex = (float)(Math.Floor(l.Latitude * precision) / precision);
                var longitudeIndex = (float)(Math.Floor(l.Longitude * precision) / precision);

                l.Latitude = latitudeIndex;
                l.Longitude = longitudeIndex;
                l.DataEstimada = data.ToString("yyy-MM-dd hh:mm");
                locais.Add(l);
            }            
            sqliteConn.Close();
          
        
            return locais;
        }

        public static void SaveJsTrackData(List<Localizacao> locais, string outJsFile)
        {
            var itLocais = locais.GroupBy(x => new { x.Latitude, x.Longitude, x.DataEstimada }).Select(group => new { Grupo = group.Key, Count = group.Count() }).OrderBy(p => p.Grupo.DataEstimada);
            using (var sw = new StreamWriter(outJsFile, true))
            {
                sw.WriteLine("g_csvString = \"lat,lon,value,time\\n\"");
                foreach (var itLocal in itLocais)
                {
                    sw.WriteLine("\"{0},{1},{2},{3}\\n\"+", itLocal.Grupo.Latitude, itLocal.Grupo.Longitude, itLocal.Count, itLocal.Grupo.DataEstimada);
                }
            }
        }

        public static string GetScriptCoordenators(List<Localizacao> locais)
        {
            var itLocais = locais.GroupBy(x => new { x.Latitude, x.Longitude, x.DataEstimada }).Select(group => new { Grupo = group.Key, Count = group.Count() }).OrderBy(p => p.Grupo.DataEstimada);
            var sb = new StringBuilder ("lat,lon,value,time\\n");
            foreach (var itLocal in itLocais)
            {
                sb.Append(string.Format( "{0},{1},{2},{3}\\n", itLocal.Grupo.Latitude, itLocal.Grupo.Longitude, itLocal.Count, itLocal.Grupo.DataEstimada));
            }
            
            return sb.ToString();
        }

        public static void SaveKsmTrackData(List<Localizacao> locais, string outKsmFile)
        {
            var itLocais = locais.GroupBy(x => new { x.Latitude, x.Longitude, x.DataEstimada }).Select(group => new { Grupo = group.Key, Count = group.Count() }).OrderBy(p => p.Grupo.DataEstimada);
           
            string dsXmlFile = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
            dsXmlFile += "<kml xmlns=\"http://www.opengis.net/kml/2.2\"  xmlns:gx=\"http://www.google.com/kml/ext/2.2\">";
            dsXmlFile += "<Document>";
            dsXmlFile += "<name>Rota do Iphone</name>";

            dsXmlFile += "  <Style id=\"pushpin\">";
            dsXmlFile += "<IconStyle id=\"mystyle\">";
            dsXmlFile += "<Icon>";
            dsXmlFile += "<href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href>";
            dsXmlFile += "<scale>1.0</scale>";
            dsXmlFile += "</Icon>";
            dsXmlFile += "</IconStyle>";
            dsXmlFile += "</Style>";

            dsXmlFile += "<Placemark id=\"mountainpin1\">";
            dsXmlFile += "<name>Pin on a mountaintop</name>"; 
            dsXmlFile += "<styleUrl>#pushpin</styleUrl>";
            dsXmlFile += "<Point>";
            dsXmlFile += "<coordinates>"+ itLocais.FirstOrDefault().Grupo.Longitude+","+itLocais.FirstOrDefault().Grupo.Latitude+"</coordinates>";
            dsXmlFile += "</Point>";
            dsXmlFile += "</Placemark>";

            dsXmlFile += "<gx:Tour>";
            dsXmlFile += "<name>Play me!</name>";

            dsXmlFile += "<gx:Playlist>";

           
                
            foreach (var itLocal in itLocais)
            {              
                dsXmlFile += "<gx:FlyTo>";
                dsXmlFile += "<gx:flyToMode>bounce</gx:flyToMode>";
                dsXmlFile += "<gx:duration>3</gx:duration>";
                dsXmlFile += "<Camera>";
                dsXmlFile += "<longitude>"+ itLocal.Grupo.Longitude+"</longitude>";
                dsXmlFile += "<latitude>>" + itLocal.Grupo.Latitude + "</latitude>";
                dsXmlFile += "<altitude>9700</altitude>";
                dsXmlFile += "<heading>-6.333</heading>";
                dsXmlFile += "<tilt>33.5</tilt>";
                dsXmlFile += "</Camera>";
                dsXmlFile += "</gx:FlyTo>";
            }
            
            dsXmlFile += "<gx:FlyTo>";
            dsXmlFile += "<gx:flyToMode>bounce</gx:flyToMode>";
            dsXmlFile += "<gx:duration>3</gx:duration>";
            dsXmlFile += "<Camera>";
            dsXmlFile += "<longitude>170.157</longitude>";
            dsXmlFile += "<latitude>-43.671</latitude>";
            dsXmlFile += "<altitude>9700</altitude>";
            dsXmlFile += "<heading>-6.333</heading>";
            dsXmlFile += "<tilt>33.5</tilt>";
            dsXmlFile += "</Camera>";
            dsXmlFile += "</gx:FlyTo>";

            dsXmlFile += "<gx:AnimatedUpdate>";
            dsXmlFile += "<gx:duration>5</gx:duration>";
            dsXmlFile += "<Update>";
            dsXmlFile += "<targetHref></targetHref>";
            dsXmlFile += "<Change>";
            dsXmlFile += "<IconStyle targetId=\"mystyle\">";
            dsXmlFile += "<scale>10.0</scale>";
            dsXmlFile += "</IconStyle>";
            dsXmlFile += "</Change>";
            dsXmlFile += "</Update>";
            dsXmlFile += "</gx:AnimatedUpdate>";

            dsXmlFile += "<gx:Wait>";
            dsXmlFile += "<gx:duration>5</gx:duration>";
            dsXmlFile += "</gx:Wait>";

            dsXmlFile += "    </gx:Playlist>";
            dsXmlFile += "</gx:Tour>";
            dsXmlFile += "</Document>";
            dsXmlFile += "</kml>";
            System.Xml.Linq.XDocument xDoc =System.Xml.Linq.XDocument.Parse(dsXmlFile);
            xDoc.Save(outKsmFile);
        }

        public static void SaveCvsTrackData(List<Localizacao> locais, string outCvsFile)
        {
            var itLocais = locais.GroupBy(x => new { x.Latitude, x.Longitude, x.DataEstimada }).Select(group => new { Grupo = group.Key, Count = group.Count() }).OrderBy(p => p.Grupo.DataEstimada);
            using (var sw = new StreamWriter(outCvsFile, true))
            {
                sw.WriteLine("lat,lon,value,time");
                foreach (var itLocal in itLocais)
                {
                    sw.WriteLine("{0},{1},{2},{3}", itLocal.Grupo.Latitude, itLocal.Grupo.Longitude, itLocal.Count, itLocal.Grupo.DataEstimada);
                }
            }
        }

    }
    public class Localizacao
    {

        public float Latitude { get; set; }
        public float Longitude { get; set; }
        public float TimeStamp2 { get; set; }
        public decimal TimeStamp { get; set; }
        public string DataEstimada { get; set; }
    }
   
}
